package io.github.talelin.sleeve.common.utills;

import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.sql.*;
import java.util.List;
import java.util.Map;

/**
 * @Description: TODO
 * @author: 周天翼
 * @date: 2020年10月12日 17:29
 */
@Slf4j
@Component
public class JdbcUtil {

    @Value("${spring.datasource.url}")
    private String url;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Resource
    private SnowFlakeUtil snowFlakeUtil;

    public void jdbcGrantSelectToUser(String tableName, String dbUser) throws SQLException {
        log.info("开始授予Select权限给虹信用户");
        String sql = "GRANT Select ON TABLE "+tableName+" TO "+dbUser;
        Connection conn = DriverManager.getConnection(url,username,password);
        conn.setAutoCommit(false);
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.execute();
        conn.commit();
        conn.close();
    }

    public void jdbcGeneralEntityImport(List<JSONObject> dataList, Map<Integer,String> indexType, String tableName) throws SQLException{
        log.info("jdbc插入{}标识数据{}条", tableName, dataList.size());
        StringBuilder sql = new StringBuilder("insert into ");
        int size = indexType.size();
        sql.append(tableName).append(" (id");
        for(int i=0;i<size;i++){
            sql.append(", ").append(indexType.get(i));
        }
        sql.append(") values (?");
        for(int i=0;i<size;i++){
            sql.append(",?");
        }
        sql.append(") ON DUPLICATE KEY UPDATE update_time = VALUES(update_time)");
        Connection conn = DriverManager.getConnection(url,username,password);
        conn.setAutoCommit(false);
        PreparedStatement preparedStatement = conn.prepareStatement(sql.toString());
        for(JSONObject data : dataList) {
            preparedStatement.setLong(1,snowFlakeUtil.nextId());
            // preparedStatement.setDate(2,new Date(System.currentTimeMillis())); 如果数据库的字段不是自动就加上
            // preparedStatement.setDate(3,new Date(System.currentTimeMillis()));
            for(int i=0,j=2;i<size;i++,j++){
                preparedStatement.setString(j,data.get(indexType.get(i)).toString());
            }
            preparedStatement.addBatch();
        }
        preparedStatement.executeBatch();
        conn.commit();
        conn.close();
    }
}


//public void jdbcGeneralEntityImport(List<JSONObject> dataList, Map<Integer,String> indexType, String tableName) throws SQLException{
//         log.info("jdbc插入{}标识数据{}条", tableName, dataList.size());
//         StringBuilder sql = new StringBuilder("insert into ");
//         int size = indexType.size();
//         sql.append(tableName).append(" (id,create_time, update_time");
//         for(int i=0;i<size;i++){
//             sql.append(", ").append(indexType.get(i));
//         }
//         sql.append(") values (?,?,?");
//         for(int i=0;i<size;i++){
//             sql.append(",?");
//         }
//         sql.append(") ON DUPLICATE KEY UPDATE update_time = VALUES(update_time)");
//         Connection conn = DriverManager.getConnection(url,username,password);
//         conn.setAutoCommit(false);
//         PreparedStatement preparedStatement = conn.prepareStatement(sql.toString());
//         for(JSONObject data : dataList) {
//             preparedStatement.setLong(1,snowFlakeUtil.nextId());
//             preparedStatement.setDate(2,new Date(System.currentTimeMillis()));
//             preparedStatement.setDate(3,new Date(System.currentTimeMillis()));
//             for(int i=0,j=4;i<size;i++,j++){
//                 preparedStatement.setString(j,data.get(indexType.get(i)).toString());
//             }
//             preparedStatement.addBatch();
//         }
//         preparedStatement.executeBatch();
//         conn.commit();
//         conn.close();
//     }